package Dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Model.queryclass;
import Util.ConnectionUtil;

public class ClassDao {
	/**
	 * 查询班级列表
	 */
	public static ArrayList<queryclass> queryClassList() {
		ArrayList<queryclass> classList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;
		try {
			// ע�� JDBC ����
			Class.forName("com.mysql.jdbc.Driver");

			// 执行查询
			System.out.println("  实例化Statement对象...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT year,classname,classid,major FROM clazz ";
			ResultSet rs = stmt.executeQuery(sql);

			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				int classid = rs.getInt("classid");
				String classname = rs.getString("classname");
				int year = rs.getInt("year");
				String major = rs.getString("major");
				queryclass queryclass = new queryclass(classid, classname, year, major);
				classList.add(queryclass);

			}
		} catch (SQLException se) {
			// 处理 JDBC 错误
			se.printStackTrace();
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
		}
		System.out.println("查询成功!");
		return classList;
	}

	// 根据班级名称查询班级的编号
	public static int getClassId(String classname) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "select classid FROM clazz where classname like '%" + classname + "%'";
		Statement stat;
		try {
			stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			if (rs.next()) {
				int class_id = rs.getInt("classid");
				return class_id;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 1;
	}

	public static void addUsePrepareStatement(queryclass clazz) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into clazz values(?,?,?,?)";
		try {
			PreparedStatement psta = conn.prepareStatement(sql);
			psta.setInt(1, clazz.getClassid());
			psta.setString(2, clazz.getClassname());
			psta.setInt(3, clazz.getYear());
			psta.setString(4, clazz.getMajor());

			int result = psta.executeUpdate();
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void del(String classname) {
		Connection conn = ConnectionUtil.getConnection();

		String sql = "delete from clazz where classname = '" + classname + "'";

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void update(String classname, int column, String value) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = null;
		switch (column) {
		case 1:
			sql = "update clazz SET classid = '" + value + "'  where classname = '" + classname + "'";
			break;

		case 2:
			sql = "update clazz SET classname = '" + value + "'  where classname = '" + classname + "'";
			break;

		case 3:
			sql = "update clazz SET year = '" + value + "'  where classname = '" + classname + "'";
			break;

		case 4:
			sql = "update clazz SET major = '" + value + "'  where classname = '" + classname + "'";
			break;

		default:
			break;
		}

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}
